#!/bin/bash

HIVE_HOME=/usr/bin/hive
#上个月1日
Last_Month_DATE=$(date -d "$(date +%Y%m)01 last month" +%Y-%m-01)

${HIVE_HOME} -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.created.files=150000;
--hive压缩
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
--写入时压缩生效
SET hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
SET hive.enforce.bucketing=true;
SET hive.enforce.sorting=true;
SET hive.optimize.bucketmapjoin=true;
SET hive.auto.convert.sortmerge.join=true;
SET hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
SET hive.exec.parallel=true;
SET hive.exec.parallel.thread.number=8;
--矢量化查询
SET hive.vectorized.execution.enabled=true;
--关联优化器
SET hive.optimize.correlation=true;
--读取零拷贝
SET hive.exec.orc.zerocopy=true;
--join数据倾斜
SET hive.optimize.skewjoin=true;
--set hive.skewjoin.key=100000;
SET hive.optimize.skewjoin.compiletime=true;
SET hive.optimize.union.remove=true;
--group倾斜
SET hive.groupby.skewindata=true;
-- 2.1 校区、学科组合分组
-- 小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id)  AS signup_num,
       itcast_school_id    AS itcast_school_id,
       itcast_school_name  AS itcast_school_name,
       itcast_subject_id   AS itcast_subject_id,
       itcast_subject_name AS itcast_subject_name,
       '-1'                AS tdepart_id,
       '-1'                AS tdepart_name,
       '-1'                AS origin_type,
       origin_type_stat    AS origin_type_stat,
       payment_time_hour   AS payment_time_hour,
       '1'                 AS grouptype,
       '1'                 AS time_type,
       payment_time_year   AS payment_time_year,
       payment_time_month  AS payment_time_month,
       payment_time_day    AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day, payment_time_hour,
         itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name;
-- 天
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id)  AS signup_num,
       itcast_school_id    AS itcast_school_id,
       itcast_school_name  AS itcast_school_name,
       itcast_subject_id   AS itcast_subject_id,
       itcast_subject_name AS itcast_subject_name,
       '-1'                AS tdepart_id,
       '-1'                AS tdepart_name,
       '-1'                AS origin_type,
       origin_type_stat    AS origin_type_stat,
       '-1'                AS payment_time_hour,
       '1'                 AS grouptype,
       '2'                 AS time_type,
       payment_time_year   AS payment_time_year,
       payment_time_month  AS payment_time_month,
       payment_time_day    AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day,
         itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name;
-- 月
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id)  AS signup_num,
       itcast_school_id    AS itcast_school_id,
       itcast_school_name  AS itcast_school_name,
       itcast_subject_id   AS itcast_subject_id,
       itcast_subject_name AS itcast_subject_name,
       '-1'                AS tdepart_id,
       '-1'                AS tdepart_name,
       '-1'                AS origin_type,
       origin_type_stat    AS origin_type_stat,
       '-1'                AS payment_time_hour,
       '1'                 AS grouptype,
       '4'                 AS time_type,
       payment_time_year   AS payment_time_year,
       payment_time_month  AS payment_time_month,
       '-1'                AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month,
         itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name;
-- 年
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id)  AS signup_num,
       itcast_school_id    AS itcast_school_id,
       itcast_school_name  AS itcast_school_name,
       itcast_subject_id   AS itcast_subject_id,
       itcast_subject_name AS itcast_subject_name,
       '-1'                AS tdepart_id,
       '-1'                AS tdepart_name,
       '-1'                AS origin_type,
       origin_type_stat    AS origin_type_stat,
       '-1'                AS payment_time_hour,
       '1'                 AS grouptype,
       '5'                 AS time_type,
       payment_time_year   AS payment_time_year,
       '-1'                AS payment_time_month,
       '-1'                AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year,
         itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name;

-- 2.2 来源渠道分组
-- 小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       origin_type        AS origin_type,
       origin_type_stat   AS origin_type_stat,
       payment_time_hour  AS payment_time_hour,
       '2'                AS grouptype,
       '1'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       payment_time_day   AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day, payment_time_hour,
         origin_type;
-- 天
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       origin_type        AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '2'                AS grouptype,
       '2'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       payment_time_day   AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day,
         origin_type;
-- 月
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       origin_type        AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '2'                AS grouptype,
       '4'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       '-1'               AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month,
         origin_type;
-- 年
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       origin_type        AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '2'                AS grouptype,
       '5'                AS time_type,
       payment_time_year  AS payment_time_year,
       '-1'               AS payment_time_month,
       '-1'               AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year,
         origin_type;

-- 2.3 咨询中心分组
-- 小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       tdepart_id         AS tdepart_id,
       tdepart_name       AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       payment_time_hour  AS payment_time_hour,
       '3'                AS grouptype,
       '1'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       payment_time_day   AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day, payment_time_hour,
         tdepart_id, tdepart_name;
-- 天
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       tdepart_id         AS tdepart_id,
       tdepart_name       AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '3'                AS grouptype,
       '2'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       payment_time_day   AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day,
         tdepart_id, tdepart_name;
-- 月
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       tdepart_id         AS tdepart_id,
       tdepart_name       AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '3'                AS grouptype,
       '4'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       '-1'               AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month,
         tdepart_id, tdepart_name;
-- 年
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       tdepart_id         AS tdepart_id,
       tdepart_name       AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '3'                AS grouptype,
       '5'                AS time_type,
       payment_time_year  AS payment_time_year,
       '-1'               AS payment_time_month,
       '-1'               AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year,
         tdepart_id, tdepart_name;

-- 2.4 总量分组
-- 小时
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       payment_time_hour  AS payment_time_hour,
       '4'                AS grouptype,
       '1'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       payment_time_day   AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day, payment_time_hour;
-- 天
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '4'                AS grouptype,
       '2'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       payment_time_day   AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month, payment_time_day;
-- 月
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '4'                AS grouptype,
       '4'                AS time_type,
       payment_time_year  AS payment_time_year,
       payment_time_month AS payment_time_month,
       '-1'               AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year, payment_time_month;
-- 年
INSERT INTO itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT COUNT(customer_id) AS signup_num,
       '-1'               AS itcast_school_id,
       '-1'               AS itcast_school_name,
       '-1'               AS itcast_subject_id,
       '-1'               AS itcast_subject_name,
       '-1'               AS tdepart_id,
       '-1'               AS tdepart_name,
       '-1'               AS origin_type,
       origin_type_stat   AS origin_type_stat,
       '-1'               AS payment_time_hour,
       '4'                AS grouptype,
       '5'                AS time_type,
       payment_time_year  AS payment_time_year,
       '-1'               AS payment_time_month,
       '-1'               AS payment_time_day
FROM itcast_dwm.customer_signup_dwm
WHERE CONCAT_WS('-', payment_time_year, payment_time_month, payment_time_day) >= ${Last_Month_DATE}
GROUP BY origin_type_stat,
         payment_time_year;
"